// 请假信息信息部分接口
var express = require('express');
var date = require("silly-datetime");
var router = express.Router();
var sqlUtils = require('../sql/sqlUtil');
let Return = require("../util/utils");
// 查询未开始请假信息|模糊查询
router.get('/', function (req, res, next) {
  var time = date.format(new Date(), 'YYYY-MM-DD');
  let page = req.query.page - 1,
    limit = req.query.limit * 1,
    begin = page * limit,
    role = req.query.role,
    schools = req.query.schools;
  if (req.query.searchParams == undefined) {
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select count(1) as count from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date>? and a.school in (${schools})`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date>? and a.school in (${schools}) order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select count(1) as count from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date>?`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date>? order by 1 desc limit ?,?`;
    }
    // 获取数据总数
    sqlUtils.sqlConnect(sqlCount, time, (err, result) => {
      if (err) {
        console.log(err);
      } else {
        var count = result[0].count;
        sqlUtils.sqlConnect(sql, [time, begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            //   循环查询学生信息
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.school.length == 0) ? '%' : result.school,
      name = (result.name.length == 0) ? '%' : result.name;
    date_begin = result.date_begin,
      date_end = result.date_end,
      club = (result.club.length == 0) ? '%' : result.club;
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select a.id,b.name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date>? and a.end_date>=? and a.end_date<=? and a.school in (${schools})`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date>? and a.end_date>=? and a.end_date<=? and a.school in (${schools}) order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select a.id,b.name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date>? and a.end_date>=? and a.end_date<=?`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date>? and a.end_date>=? and a.end_date<=? order by 1 desc limit ?,?`;
    }
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect(sqlCount, [time, date_begin, date_end], function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data.length;
        sqlUtils.sqlConnect(sql, [time, date_begin, date_end, begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});
// 查询已开始请假信息|模糊查询
router.get('/pass', function (req, res, next) {
  var time = date.format(new Date(), 'YYYY-MM-DD');
  let page = req.query.page - 1,
    limit = req.query.limit * 1,
    begin = page * limit,
    role = req.query.role,
    schools = req.query.schools;
  if (req.query.searchParams == undefined) {
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select count(1) as count from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date<=? and a.school in (${schools})`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date<=? and a.school in (${schools}) order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select count(1) as count from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date<=?`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.begin_date<=? order by 1 desc limit ?,?`;
    }
    // 获取数据总数
    sqlUtils.sqlConnect(sqlCount, time, (err, result) => {
      if (err) {
        console.log(err);
      } else {
        var count = result[0].count;
        sqlUtils.sqlConnect(sql, [time, begin, limit], function (err, data) {
          if (err) {
            console.log(err);
          } else {
            //   循环查询学生信息
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  } else {
    var result = JSON.parse(req.query.searchParams);
    var s_name = (result.school.length == 0) ? '%' : result.school,
      name = (result.name.length == 0) ? '%' : result.name;
    date_begin = result.date_begin,
      date_end = result.date_end,
      club = (result.club.length == 0) ? '%' : result.club;
    // 判断是员工还是管理员
    let sqlCount = null,
      sql = null;
    if (role == 0) {
      schools = '"' + schools.split(",").join('","') + '"';
      // schools=schools.split(",").join('","');
      sqlCount = `select a.id,b.name from leave_info as a inner join student as b on a.stu_id=b.id and a.is_del=0 and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date<=? and a.end_date>=? and a.end_date<=? and a.school in (${schools})`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date<=? and a.end_date>=? and a.end_date<=? and a.school in (${schools}) order by 1 desc limit ?,?`;
    } else {
      sqlCount = `select a.id,b.name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date<=? and a.end_date>=? and a.end_date<=?`;
      sql = `select a.id,a.club,a.begin_date,a.end_date,b.name,b.nickname,b.mobile,a.school,a.grade,a.class_name from leave_info as a inner join student as b on a.stu_id=b.id and a.club like '%${club}%' and  a.school like '%${s_name}%' and b.name like '%${name}%' and a.is_del=0 and a.begin_date<=? and a.end_date>=? and a.end_date<=? order by 1 desc limit ?,?`;
    }
    // 计算表中符合条件信息数量
    sqlUtils.sqlConnect(sqlCount, [time, date_begin, date_end], function (err, data) {
      if (err) {
        console.log(err);
      } else {
        var count = data.length;
        sqlUtils.sqlConnect(sql, [time, date_begin, date_end, begin, limit], function (err, data) {
          if (err) {
            console.log(err)
          } else {
            var json = Return.setResult();
            json.code = 0;
            json.count = count;
            json.data = data;
            res.send(json);
          }
        })
      }
    })
  }
});

// 删除请假记录
router.delete('/delete', (req, res) => {
  const id = req.body.id;
  if (id) {
    sqlUtils.sqlConnect("update leave_info set is_del=1 where id=?", [id], (err) => {
      if (err) {
        res.send(Return.setResult(500, "系统错误，请稍后重试!"))
      } else {
        res.send(Return.setResult())
      }
    })
  }
})
module.exports = router;